from openpyxl import load_workbook
from openpyxl_image_loader import SheetImageLoader
import os
from datetime import datetime
from openpyxl.utils import range_boundaries
from util.common import folder_path
import numpy as np
from PIL import Image

#来源：https://blog.csdn.net/weixin_44380466/article/details/131864657
#来源：https://blog.csdn.net/weixin_46783197/article/details/139320666
#来源：https://blog.csdn.net/weixin_52710755/article/details/136543199
folder_path = folder_path('excel')

# 定义一个每个表格有多少数据的
excel_total_data = [175,139,58,9,24,31,39,66,19,17]

# 选择工作表
workbook = load_workbook('../upload/excel/sample/product.xlsx')
sheet_names = workbook.sheetnames
for key,item in enumerate(sheet_names):
    if key !=4 :
        continue
    sheet = workbook[item]
    image_loader = SheetImageLoader(sheet)
    path = folder_path + "/" + item
    if not os.path.exists(path):
        os.makedirs(path)

    # 获取合并单元格数据的最大值和最小值
    merged_cell_range = list(sheet.merged_cells.ranges)
    merged_cell_C2 = [cell for cell in merged_cell_range if cell.coord[0]=='C']
    merged_data = []
    for merged_cell in merged_cell_C2:
        min_col, min_row, max_col, max_row = merged_cell.bounds
        obj = {'min':min_row,'max':max_row} 
        merged_data.append(obj)
    print(item,"merge_data",merged_data)

    # excel表中id,barcode字典
    excel_barcodeList = ['0']
    for index, row in enumerate(sheet.rows, start=1):
        data = {"id": index,"barcode":row[3].value}
        if index < excel_total_data[key]:
            excel_barcodeList.append(row[3].value)
    print(excel_barcodeList,len(excel_barcodeList))
    

    images = sheet._images
    # print(images,len(images),images[0].anchor.to.row)
    # exit(images[0].anchor.to.row)
    image_positions = {}
    index = 0
    output_folder = 'test/'

    for image in images:
        img_row = image.anchor.to.row
        img_column = image.anchor.to.col

        for index, row in enumerate(sheet.rows, start=1):
            if row[3].value == None:
                continue
            name = row[3].value
            print(index,name,img_row)
            if img_row == index:
                print(img_row)

                # data = image.ref
                # if name != None :
                #     if '\n' in name:
                #         name = name.split('\n')[0]
                #     with open(os.path.join(output_folder, pic_name), 'wb') as img_file:
                #     img_pil = Image.open(image.ref).convert("RGB")
                #     img_array = np.array(img_pil)
                #     # 将 NumPy 数组保存为图像文件
                #     img_pil.save(path+'/'+ name + ".png")
                #     for item in merged_data:
                #         if(index >= item['min'] and index <= item['max']):
                #             for i in range(item['min'], item['max']+1):
                #                 name = excel_barcodeList[i]
                #                 if name != None :
                #                     if '\n' in name:
                #                         name = name.split('\n')[0]
                #                     image.save(path+'/'+ name + ".png")

        image_loader._images.clear()
    workbook.close()
